﻿using System;
using System.Text;
using System.Data;
using System.Collections.Generic;

using MySql.Data.MySqlClient;
using Think.Lenovo.Com.Models;
using Think.Lenovo.Com.Common;
using Think.Lenovo.Com.Models.Questionnaire;

namespace Think.Lenovo.Com.DataAccess.Questionnaire
{
    public class Questionnaire
    {
        /// <summary>
        /// 添加一个问卷
        /// </summary>
        /// <param name="model">问卷实体对象</param>
        /// <returns>问卷id</returns>
        public long AddQuestionnaire(QuestionnaireModel model)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("insert into ")
                .Append(" tbl_questionnaire (Name, Description, CreateUserId, CreateDateTime, ModifyDateTime, ModifyUserId)")
                .Append(" values (@Name,@Description,@CreateUserId,@CreateDateTime, @ModifyDateTime, @ModifyUserId)");

            MySqlParameter[] ps = new MySqlParameter[]{ 
                    new MySqlParameter("@Name",MySqlDbType.VarChar,50),
                    new MySqlParameter("@Description",MySqlDbType.Text),
                    new MySqlParameter("@CreateUserId",MySqlDbType.Int32),
                    new MySqlParameter("@CreateDateTime",MySqlDbType.DateTime),
                    new MySqlParameter("@ModifyDateTime",MySqlDbType.DateTime),
                    new MySqlParameter("@ModifyUserId",MySqlDbType.Int32)
                };

            ps[0].Value = model.Name;
            ps[1].Value = model.Description;
            ps[2].Value = model.CreateUserId;
            ps[3].Value = model.CreateDateTime;
            ps[4].Value = model.ModifyDateTime;
            ps[5].Value = model.ModifyUserId;

            long id = MysqlHelper.Insert(conn, sql.ToString(), ps);
            return id;
        }
        /// <summary>
        /// 修改一个问卷
        /// </summary>
        /// <param name="model">问卷实体对象</param>
        /// <returns>受影响的行数</returns>
        public int UpdateQuestionnaire(QuestionnaireModel model)
        {

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Update ")
                .Append("  tbl_questionnaire Set  Name=@Name, Description=@Description,")
                .Append("CreateUserId=@CreateUserId,")
                .Append("CreateDateTime=@CreateDateTime, ")
                .Append("ModifyDateTime=@ModifyDateTime, ModifyUserId=@ModifyUserId ")
                .Append(" Where Id=@Id");


            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@Name",MySqlDbType.VarChar,50),
                    new MySqlParameter("@Description",MySqlDbType.Text),
                    new MySqlParameter("@CreateUserId",MySqlDbType.Int32),
                    new MySqlParameter("@CreateDateTime",MySqlDbType.DateTime),
                    new MySqlParameter("@ModifyDateTime",MySqlDbType.DateTime),
                    new MySqlParameter("@ModifyUserId",MySqlDbType.Int32),
                    new MySqlParameter("@Id",MySqlDbType.Int32)
                };

            ps[0].Value = model.Name;
            ps[1].Value = model.Description;
            ps[2].Value = model.CreateUserId;
            ps[3].Value = model.CreateDateTime;
            ps[4].Value = model.ModifyDateTime;
            ps[5].Value = model.ModifyUserId;
            ps[6].Value = model.Id;
            int affect = MysqlHelper.Update(conn, sql.ToString(), ps);
            return affect;

        }
        /// <summary>
        /// 得到一个问卷实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public QuestionnaireModel GetQuestionnaire(int id)
        {
            QuestionnaireModel model = new QuestionnaireModel();
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(56);
            sql.Append("Select * From tbl_questionnaire Where Id=" + id);
            DataSet ds = MysqlHelper.Query(conn, sql.ToString());
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        DateTime now = DateTime.Now;
                        model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                        model.Name = Convert.ToString(ds.Tables[0].Rows[0]["Name"]);
                        model.Description = Convert.ToString(ds.Tables[0].Rows[0]["Description"]);
                        model.CreateUserId = Convert.ToInt32(ds.Tables[0].Rows[0]["CreateUserId"]);
                        DateTime.TryParse(ds.Tables[0].Rows[0]["CreateDateTime"].ToString(), out now);
                        model.CreateDateTime = now;
                        DateTime.TryParse(ds.Tables[0].Rows[0]["CreateDateTime"].ToString(), out now);
                        model.ModifyDateTime = now;
                        model.ModifyUserId = Convert.ToInt32(ds.Tables[0].Rows[0]["ModifyUserId"]);
                    }
                }
            }
            return model;
        }
        /// <summary>
        /// 获得pageSize条数据
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示数据数量</param>
        /// <returns></returns>
        public LinkedList<QuestionnaireModel> GetQuestionnaires(int pageIndex, int pageSize)
        {
            LinkedList<QuestionnaireModel> list = new LinkedList<QuestionnaireModel>();
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(56);
            if (pageIndex < 1)
            {
                pageIndex = 1;
            }
            if (pageSize < 1)
            {
                pageSize = 10;
            }
            sql.Append("Select * From tbl_questionnaire order by Id desc limit ")
                .Append((pageIndex - 1) * pageSize)
                .Append(",")
                .Append(pageSize);
            DataSet ds = MysqlHelper.Query(conn, sql.ToString());
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        foreach (DataRow row in ds.Tables[0].Rows)
                        {
                            QuestionnaireModel model = new QuestionnaireModel();
                            DateTime now = DateTime.Now;
                            model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                            model.Name = Convert.ToString(ds.Tables[0].Rows[0]["Name"]);
                            model.Description = Convert.ToString(ds.Tables[0].Rows[0]["Description"]);
                            model.CreateUserId = Convert.ToInt32(ds.Tables[0].Rows[0]["CreateUserId"]);
                            DateTime.TryParse(ds.Tables[0].Rows[0]["CreateDateTime"].ToString(), out now);
                            model.CreateDateTime = now;
                            DateTime.TryParse(ds.Tables[0].Rows[0]["CreateDateTime"].ToString(), out now);
                            model.ModifyDateTime = now;
                            model.ModifyUserId = Convert.ToInt32(ds.Tables[0].Rows[0]["ModifyUserId"]);
                            list.AddLast(model);
                        }
                    }
                }
            }
            return list;
        }
    }
}
